# -*- coding:utf-8 -*-

"""
  @brief SentiWordNet v3.0 CSV(TSV) to SQLite3 converter
  @author Shintaro Takemura(stakemura@gmail.com)
 
  This code is public domain, no warranty expressed or implied, 
  Functionality is thought to be correct, but it's up to you to make
  sure it does what you want.
"""

import os
import sqlite3

# Open Database
def openDB(file_path):
    db = sqlite3.connect(
        os.path.join(os.path.dirname(__file__), file_path),
        detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES )
    db.row_factory = sqlite3.Row

    db.execute("""
        CREATE TABLE IF NOT EXISTS synset_pn(
            synset varchar(10) not null,
            positive integer not null,
            negative integer not null,
            gloss varchar(256) not null)""")
    db.execute("CREATE INDEX if not exists 'pn_synset_idx' ON 'synset_pn' (synset)")
    
    db.execute("""
        CREATE TABLE IF NOT EXISTS synset_term(
            synset varchar(10) not null,
            lemma varchar(32) not null,
            number integer not null)""")
    db.execute("CREATE INDEX if not exists 'term_synset_idx' ON 'synset_term' (synset)")

    return db

class CommentedFile:
    def __init__(self, f, commentstring="#"):
        self.f = f
        self.commentstring = commentstring

    def next(self):
        line = self.f.next()
        while line.startswith(self.commentstring):
            line = self.f.next()
        return line

    def __iter__(self):
        return self

def main():
    from optparse import OptionParser
    import csv

    parser = OptionParser()
    parser.add_option("-i", "--input", dest="input",
      default="./SentiWordNet_3.0.0_20100908.txt",
      help="Original CSV(TSV) file", metavar="FILE")
    parser.add_option("-o", "--output", dest="output",
      default="./SentiWordNet_3.0.0_20100908.sqlite",
      help="SQLite3 file", metavar="FILE")
        
    options, args = parser.parse_args()
    
    # parse SentiWordNet v3.0 CSV
    try:
        f = open(options.input,'rb')
        db = openDB(options.output)
        cur = db.cursor()
        try:
            for row in csv.reader(CommentedFile(f), delimiter='\t', quotechar='|'):
                if len(row)!=6:
                    continue
                if len(row[0])!=1:
                    continue
                
                #print(row)
                synset = row[1]+'-'+row[0]
                positive = int(float(row[2])*8)
                negative = int(float(row[3])*8)
                gloss = (row[5])
                cur.execute(
                    'insert into synset_pn values (?,?,?,?)',
                    (synset,positive,negative,gloss))
                
                for term in row[4].split(' '):
                    lemma, number = tuple(term.split('#'))                    
                    cur.execute(
                        'insert into synset_term values (?,?,?)',
                        (synset, lemma, number) )
                        
            db.commit()
        except Exception, ex:
            print(ex)
            db.rollback()
            raise
        finally:
            cur.close()
        db.close()
        f.close()
    except IOError, (errno, msg):
        print('except: Cannot open "%s"' % options.input)
        print('  errno: [%d] msg: [%s]' % (errno, msg))
    
if __name__ == "__main__":
    main()
